clear all
set more off 
cd "$main_dir/data"

********************************************************************************
* building summary statistics shown in table 1

********************************************************************************

****
* Read data used for Tables 1 and 2
use data_for_tables_1_2.dta, clear

* For all samples
forvalues buildingtype = 1 / 6 {

	use temp_table_all.dta, clear

	**
	* 50+ units only
	summ units, d
	tab units50, missing
	keep if units >= 50 | missing(units)
	tab units50, missing


	**
	* Create building / neighborhood type
	capture noisily drop buildingtype
	gen buildingtype = ""


	if `buildingtype' == 1 replace buildingtype = "1) no buildings, all" if allsample == 0
	if `buildingtype' == 2 replace buildingtype = "2) buildings, all" if allsample == 1
	if `buildingtype' == 3 replace buildingtype = "3) no buildings, low inc" if allsample == 0 & lowinc == 1 & student_tract == 0
	if `buildingtype' == 4 replace buildingtype = "4) buildings, low inc" if allsample == 1 & lowinc == 1 & student_tract == 0
	if `buildingtype' == 5 replace buildingtype = "5) buildings, low inc, 2014-2016" if allsample == 1 & lowinc == 1 & student_tract == 0 & inlist(yearbuilt, 2014, 2015, 2016)
	if `buildingtype' == 6 replace buildingtype = "6) final" if allsample == 1 & lowinc == 1 & student_tract == 0 & inlist(yearbuilt, 2014, 2015, 2016) & final_sample == 1
	
	tab buildingtype, missing

	keep if ~missing(buildingtype)


	**
	* Create city vars for collapse
	levelsof city, local(allcities) clean

	capture noisily drop brooklyn

	foreach tempvar in `allcities' {
		gen `tempvar' = (city == "`tempvar'")
		tab `tempvar', missing
	}


	**
	* Collapse

	gen obs = 1

	collapse (mean) units rent_600 rent_1br_600 (p25) units_p25 rent_600_p25 rent_1br_600_p25 (p50) units_p50 rent_600_p50 rent_1br_600_p50 (p75) units_p75 rent_600_p75 rent_1br_600_p75 (sum) obs, by(buildingtype)

	xpose, clear varname

	order _varname
	rename _varname neighborhood_char

	drop if neighborhood_char == "buildingtype"

	format v1 %10.0f

	if `buildingtype' == 1 rename v1 none_allinc
	if `buildingtype' == 2 rename v1 all_allinc
	if `buildingtype' == 3 rename v1 none_lowinc
	if `buildingtype' == 4 rename v1 all_lowinc
	if `buildingtype' == 5 rename v1 y20142016_lowinc
	if `buildingtype' == 6 rename v1 final


	* Preserve ordering in final table
	gen temp = _n
	order temp
	
	* Update ordering

	* Units
	replace temp = 2 if neighborhood_char == "units_p25"
	replace temp = 3 if neighborhood_char == "units_p50"
	replace temp = 4 if neighborhood_char == "units_p75"

	* Rents 1 br
	replace temp = 5 if neighborhood_char == "rent_1br_600"
	replace temp = 6 if neighborhood_char == "rent_1br_600_p25"
	replace temp = 7 if neighborhood_char == "rent_1br_600_p50"
	replace temp = 8 if neighborhood_char == "rent_1br_600_p75"

	* Rents all
	replace temp = 9 if neighborhood_char == "rent_600"
	replace temp = 10 if neighborhood_char == "rent_600_p25"
	replace temp = 11 if neighborhood_char == "rent_600_p50"
	replace temp = 12 if neighborhood_char == "rent_600_p75"

	assert temp == 13 if neighborhood_char == "atlanta"

	gsort temp neighborhood_char
	save buildingtype_all_`buildingtype'.dta, replace


}

*  Merge all building types together and save final table

use buildingtype_all_1.dta, clear

forvalues buildingtype = 2 / 6 {

	merge 1:1 temp neighborhood_char using buildingtype_all_`buildingtype'.dta
	assert _merge == 3
	drop _merge

	gsort temp neighborhood_char

}

gsort temp
drop temp

* the exported data set is the table 
export excel using table_1.xlsx, firstrow(variables) replace
